package com.roc.web.action.merchangerecord;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;

public class MerchantsChangeRecordSearchCondition extends BaseSearchCondition{
	
	private String mchtCd;
	private String nameOld;
	private String phoneNo;
	private String accountNoOld;
	private String createatMin;
	private String  createatMax;
	
	

	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		if(Constance.isNotEmpty(mchtCd))
		{
			filedBeanList.add(new WhereHQLFieldBean("d.mcht_cd", mchtCd.trim(),"=", false));
		}
		if(Constance.isNotEmpty(nameOld))
		{
			filedBeanList.add(new WhereHQLFieldBean("a.nameold", nameOld.trim(),"=", false));
		}
		if(Constance.isNotEmpty(accountNoOld))
		{
			filedBeanList.add(new WhereHQLFieldBean("a.accountnoold", accountNoOld.trim(),"=", false));
		}
		if(Constance.isNotEmpty(phoneNo))
		{
			filedBeanList.add(new WhereHQLFieldBean("a.phoneno ", phoneNo.trim(),"=", false));
		}
		if(createatMin!=null&&!"".equals(createatMin)){
			filedBeanList.add(new WhereHQLFieldBean("a.createat >=to_date( '"+createatMin.trim()+"','yyyy-MM-dd HH24:mi:ss')"));
		}
		if(createatMax!=null&&!"".equals(createatMax)){
			filedBeanList.add(new WhereHQLFieldBean("a.createat <=to_date( '"+createatMax.trim()+"','yyyy-MM-dd HH24:mi:ss')"));
		}
		
		return filedBeanList;
	}

	@Override
	protected String getRowCountHQL() {
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT COUNT(1) ")
		  .append(" from t_dypay_bankaccount_his a ")
		  .append("  left join t_dypay_bankinfo b on a.bankidold = b.bankbranchid  ")
		  .append("  left join t_dypay_bankinfo c on a.bankidnew = c.bankbranchid ")
		  .append("  left join tbl_mcht_inf d on a.phoneno = d.contact_phoneno and d.group_cd = '3' ")
		  .append("  RIGHT JOIN   ")
		  .append(" ( SELECT  t.phoneno ,max(t.createat) createat FROM t_dypay_bankaccount_his   t " );
				  if(createatMin!=null&&!"".equals(createatMin)){
						 sb.append( " WHERE  t.createat >=to_date( '"+createatMin.trim()+"','yyyy-MM-dd HH24:mi:ss') "); 
						 sb.append( " and  t.createat <=to_date( '"+createatMax.trim()+"','yyyy-MM-dd HH24:mi:ss') ");  
					}
				  sb.append( "GROUP BY t.phoneno) x ")
		  .append("  ON a.phoneno=x.phoneno AND  a.createat =x.createat  ")
		  .append(" where 1=1   ");
		
		
		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT  ")
		  .append(" d.mcht_cd,a.nameold,a.phoneno, a.accountnoold,b.bankname banknameold,  ")
		  .append(" a.bankidold,a.accountnonew,c.bankname banknamenew,a.bankidnew,to_char(a.createat,'yyyy-MM-dd HH24:mi:ss') ")
		  .append(" from t_dypay_bankaccount_his a ")
		  .append("  left join t_dypay_bankinfo b on a.bankidold = b.bankbranchid  ")
		  .append("  left join t_dypay_bankinfo c on a.bankidnew = c.bankbranchid ")
		  .append("  left join tbl_mcht_inf d on a.phoneno = d.contact_phoneno and d.group_cd = '3' ")
		  .append("   RIGHT JOIN   ")
		  .append(" ( SELECT  t.phoneno ,max(t.createat) createat FROM t_dypay_bankaccount_his   t" );
		if(createatMin!=null&&!"".equals(createatMin)){
			 sb.append( " WHERE  t.createat >=to_date( '"+createatMin.trim()+"','yyyy-MM-dd HH24:mi:ss') "); 
			 sb.append( " and   t.createat <=to_date( '"+createatMax.trim()+"','yyyy-MM-dd HH24:mi:ss') ");  
		}
		  	sb.append( " GROUP BY t.phoneno) x ")
		  .append("  ON a.phoneno=x.phoneno AND  a.createat =x.createat  ")
		  .append(" where 1=1   ");
		return sb.toString();
	}

	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		return null;
	}

	public void setOpId(String valueOf) {
		// TODO Auto-generated method stub
		
	}

	public String getMchtCd() {
		return mchtCd;
	}

	public void setMchtCd(String mchtCd) {
		this.mchtCd = mchtCd;
	}

	public String getNameOld() {
		return nameOld;
	}

	public void setNameOld(String nameOld) {
		this.nameOld = nameOld;
	}

	public String getPhoneNo() {
		return phoneNo;
	}

	public void setPhoneNo(String phoneNo) {
		this.phoneNo = phoneNo;
	}

	public String getAccountNoOld() {
		return accountNoOld;
	}

	public void setAccountNoOld(String accountNoOld) {
		this.accountNoOld = accountNoOld;
	}

	public String getCreateatMin() {
		return createatMin;
	}

	public void setCreateatMin(String createatMin) {
		this.createatMin = createatMin;
	}

	public String getCreateatMax() {
		return createatMax;
	}

	public void setCreateatMax(String createatMax) {
		this.createatMax = createatMax;
	}

	
	
	


	
}
